package com.topideal.supplychain.hades.mapper;

import com.topideal.supplychain.hades.dto.ProjectViewDto;
import com.topideal.supplychain.hades.viewfilter.BasicInfoFilter;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * 项目工时统计
 *
 * @author xuxiaoyan
 * @date 2018-09-12 10:05
 */
@Mapper
public interface ProjectListMapper {

    /**
     * 按项目、模块维度统计工时
     *
     * @param basicInfoFilter
     * @return
     */
    @Select("<script> " +
            "SELECT p.pname as pName, p.ID as pId, c.cname as cName, c.ID as cId, SUM(js.TIMESPENT)/3600 as pTotalTimeSpent "
            + "FROM jiraissue js "
            + "left join nodeassociation na on na.SOURCE_NODE_ID = js.ID "
            + "left join component c on c.ID = na.SINK_NODE_ID "
            + "LEFT join project p on js.PROJECT = p.ID "
            + " <where> "
            + " na.ASSOCIATION_TYPE='IssueComponent' "
            + " <if test=\"pId != null and pId != '' \"> and js.PROJECT = #{pId} </if> "
            + " <if test=\"cId != null and cId != '' \"> and c.ID = #{cId} </if> "
            + "	<if test=\"createDateFromStr != null and createDateFromStr != '' \"> and js.CREATED >= #{createDateFromStr}</if>"
            + "	<if test=\"createDateToStr != null and createDateToStr != '' \"> and #{createDateToStr} >= js.CREATED</if>"
            + " </where> "
            + " GROUP BY p.pname, c.cname"
            + " </script> ")
    List<ProjectViewDto> selectProjectTimeSummary(BasicInfoFilter basicInfoFilter);


    /**
     * 按项目维度查询项目下jira任务及工时详情
     *
     * @param basicInfoFilter
     * @return
     */
    @Select("<script> " +
            "SELECT concat(p.pkey,\"-\",js.issuenum) as jiraNum, js.ID as issueId, p.pname as pName, cp.cpn as cName, js.SUMMARY as title, "
            + "js.issuetype as issueType, js.issuestatus as issueStatus, js.CREATED as issueCreateTime, js.REPORTER as reporter, js.TIMESPENT/3600 as iTotalTimeSpent "
            + "FROM jiraissue js "
            + "left join (SELECT na.SOURCE_NODE_ID, c.cname cpn "
            + "FROM component c INNER join nodeassociation na on c.ID = na.SINK_NODE_ID "
            + "WHERE na.ASSOCIATION_TYPE = 'IssueComponent') cp on cp.SOURCE_NODE_ID = js.id "
            + "LEFT join project p on js.PROJECT = p.ID"
            + " <where> "
            + " <if test=\"pId != null and pId != '' \"> and js.PROJECT = #{pId} </if> "
            + "	<if test=\"createDateFromStr != null and createDateFromStr != '' \"> and js.CREATED >= #{createDateFromStr}</if>"
            + "	<if test=\"createDateToStr != null and createDateToStr != '' \"> and #{createDateToStr} >= js.CREATED</if>"
            + " </where> "
            + "order by js.issuenum"
            + " </script> ")
    List<ProjectViewDto> selectProjectTimeDetail(BasicInfoFilter basicInfoFilter);


    /**
     * 批量查询自定义字段信息
     *
     * @param issueIds
     * @param customfieldId
     * @return
     */
    @Select("<script> " +
            "SELECT c.cfname, cv.STRINGVALUE as stringValue, cv.DATEVALUE as dateValue, cv.ISSUE as issueId "
            + "FROM customfieldvalue cv "
            + "inner join customfield c on cv.CUSTOMFIELD = c.ID "
            + "<where> "
            + "c.ID = #{customfieldId} "
            + "and cv.ISSUE in "
            + "<foreach collection=\"issueIds\" item=\"item\" index=\"index\" open=\"(\" separator=\",\" close=\")\">#{item}</foreach>"
            + " </where> "
            + " </script> ")
    List<ProjectViewDto> selectCustomfieldDetail(@Param("issueIds") List<BigDecimal> issueIds, @Param("customfieldId") String customfieldId);


    /**
     * 批量查询时间节点信息
     *
     * @param issueIds
     * @param issueStatus
     * @return
     */
    @Select("<script> " +
            "SELECT cg.CREATED as changeItemTime, cg.issueid as issueId "
            + "FROM changegroup cg "
            + "INNER JOIN changeitem ci ON cg.ID = ci.GROUPID "
            + "<where> "
            + "ci.FIELD = 'status' "
            + "and ci.NEWVALUE = #{issueStatus} "
            + "and cg.issueid in "
            + "<foreach collection=\"issueIds\" item=\"item\" index=\"index\" open=\"(\" separator=\",\" close=\")\">#{item}</foreach>"
            + " </where> "
            + "ORDER BY changeItemTime DESC LIMIT 1"
            + " </script> ")
    List<ProjectViewDto> selectChangeGroupDetail(@Param("issueIds") List<BigDecimal> issueIds, @Param("issueStatus") String issueStatus);

    /**
     * 批量查询任务的开发/测试登记工时
     *
     * @param issueId
     * @param author
     * @return
     */
    @Select("<script> " +
            "select SUM(timeworked)/3600 as timeWorked, issueid as issueid "
            + "from worklog "
            + "<where> "
            + "AUTHOR = #{author} "
            + "and issueid = #{issueId} "
            + " </where> "
            + " </script> ")
    ProjectViewDto selectWorkLogDetail(@Param("issueId") BigDecimal issueId, @Param("author") String author);

    /**
     * 查找中文名
     *
     * @param userKey
     * @return
     */
    @Select("<script> " +
            "select cu.display_name "
            + "from app_user au "
            + "inner join cwd_user cu on au.lower_user_name = cu.lower_user_name "
            + "inner join cwd_directory dir on dir.id = cu.directory_id "
            + "<where>"
            + "au.user_key = #{userKey} "
            + " </where> "
            + "order by dir.directory_position LIMIT 1"
            + " </script> ")
    String selectChinaName(@Param("userKey") String userKey);

    /**
     * 按模块维度查询jira任务及工时详情
     *
     * @param filter
     * @return
     */
    @Select("<script> " +
            "SELECT concat(p.pkey,\"-\",js.issuenum) as jiraNum, js.ID as issueId, p.pname as pName, cp.cpn as cName, js.SUMMARY as title, js.issuetype as issueType, "
            + "js.issuestatus as issueStatus, js.CREATED as issueCreateTime, js.REPORTER as reporter, js.TIMESPENT/3600 as iTotalTimeSpent "
            + "FROM jiraissue js "
            + "left join (SELECT na.SOURCE_NODE_ID jid, na.SINK_NODE_ID cid, c.cname cpn "
            + "FROM component c INNER join nodeassociation na on c.ID = na.SINK_NODE_ID "
            + "WHERE na.ASSOCIATION_TYPE = 'IssueComponent') cp on cp.jid = js.id "
            + "LEFT join project p on js.PROJECT = p.ID"
            + " <where> "
            + " <if test=\"cId != null and cId != '' \"> and cp.cid = #{cId} </if> "
            + "	<if test=\"createDateFromStr != null and createDateFromStr != '' \"> and js.CREATED >= #{createDateFromStr}</if>"
            + "	<if test=\"createDateToStr != null and createDateToStr != '' \"> and #{createDateToStr} >= js.CREATED</if>"
            + " </where> "
            + "order by js.issuenum"
            + " </script> ")
    List<ProjectViewDto> selectComponentTimeDetail(BasicInfoFilter filter);
}
